--****************************************************************************
--Table: SinhVien
--****************************************************************************

--select
if exists(select name from sysobjects where name='sp_Select_SinhVien')
drop proc sp_Select_SinhVien
go
create proc sp_Select_SinhVien
as 
begin
	select * from SINHVIEN
end

--insert
if exists(select name from sysobjects where name='sp_Insert_SinhVien')
drop proc sp_Insert_SinhVien
go
create proc sp_Insert_SinhVien
	@MaSinhVien varchar(7),
	@HoTenSV nvarchar(31),
	@Email nvarchar(51),
	@DTB float
as 
begin
	begin tran
	if(exists(select * from SinhVien where MaSinhVien=@MaSinhVien))
		begin
			raiserror (N'Thông tin cần thêm vào đã có trong CSDL!!!', 16,1)
			rollback tran
			return
		end

		else
		begin
			insert into SinhVien values(@MaSinhVien,@HoTenSV,@Email,@DTB)
			commit tran
		end
end

--delete
if exists(select name from sysobjects where name='sp_Delete_SinhVien')
drop proc sp_Delete_SinhVien
go
create proc sp_Delete_SinhVien
	@MaSinhVien varchar(7)
as 
begin
	begin tran
	if not exists(select* from SinhVien where MaSinhVien=@MaSinhVien)
	begin
		raiserror (N'Thông tin cần xoá không tồn tại!!!',16,1)
		rollback tran
		return
	end
	else --if( exists (select * from SinhVien where MaSinhVien = @MaSinhVien))
		begin 
			Delete from SinhVien where MaSinhVien = @MaSinhVien
			commit tran			
		end 
end

--Update
if exists(select name from sysobjects where name='sp_Update_SinhVien')
drop proc sp_Update_SinhVien
go
create proc sp_Update_SinhVien
	@MaSinhVien varchar(7),
	@HoTenSV nvarchar(31),
	@Email nvarchar(51),
	@DTB float
as 
begin
	begin tran
	begin
		Update SinhVien set HoTenSV=@HoTenSV,Email=@Email,DTB=@DTB
		where MaSinhVien = @MaSinhVien
		commit tran	
	end 
end


